package com.hsnn.medstgmini.pro.base.dao.Impl;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.dict.dao.impl.BaseHibernateDAO;
import com.hsnn.medstgmini.base.std.model.StdHealthbureau;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.pro.base.dao.BaseBakpurchaseDao;
import com.hsnn.medstgmini.pro.base.model.BaseBakpurchase;
import com.hsnn.medstgmini.pro.base.model.ViewBakpurchase;
import com.hsnn.medstgmini.sys.model.SysUser;
import com.hsnn.medstgmini.sys.model.UserType;
import com.hsnn.medstgmini.util.Pagination;
import com.hsnn.medstgmini.util.ParseDate;

/**
 * @category 备案采购接口DAO实现类
 * @author 韩守松
 * @date 2015年6月19日
 */
@Repository
public class BaseBakpurchaseDaoImpl extends BaseHibernateDAO implements BaseBakpurchaseDao {

	private static final Logger log = Logger.getLogger(BaseBakpurchaseDaoImpl.class);
	
	@Autowired
	private HibernateSqlUtil dao;
	
	/* 长期备案复审设置 0 - 不需要复审， 1 - 需要复审 */
	@Value("#{sysProp['bak.longterm']}")
    private String bakLongterm;
	
	/* 临时备案复审设置 0 - 不需要复审， 1 - 需要复审 */
	@Value("#{sysProp['bak.tamperary']}")
    private String bakTamperary;

	/**
	 * @category 查询备案审批信息
	 * @author 韩守松
	 * @date   2015年6月19日
	 * @param  @param page
	 * @param  @param viewBakpurchase
	 * @param  @param date1
	 * @param  @param date2
	 * @param  @return
	 */
	@SuppressWarnings("unchecked")
	public List<ViewBakpurchase> getBaseBakpurchaseList(Pagination page, ViewBakpurchase viewBakpurchase,Timestamp date1,Timestamp date2){
		try{
		StringBuffer hql = new StringBuffer();
		StringBuffer hqlCount = new StringBuffer();
		String compId = viewBakpurchase.getCompId();//生产企业名称
		String bakPurId = viewBakpurchase.getBakPurId();//备案id
		
		String hospName = viewBakpurchase.getHospName();//医疗机构名称
		Short auditStatus = viewBakpurchase.getAuditStatus();//审核状态
		Short isLongtimePlan = viewBakpurchase.getIsLongtimePlan();//是否长期
		
		String bakHospId = viewBakpurchase.getBakHospId();//医疗机构id
		String bakSubmitUser = viewBakpurchase.getBakSubmitUser();//申请人
		String drugName = viewBakpurchase.getDrugName();//通用名
		String formId = viewBakpurchase.getFormId();//剂型
		String specId = viewBakpurchase.getSpecId();//规格
		hql.append("from ViewBakpurchase bb where 1=1 ");
		hqlCount.append("select count(*) from  ViewBakpurchase bb where 1=1 ");
		if(StringUtils.isNotEmpty(bakPurId)){
			hql.append(" and bb.bakPurId = :bakPurId ");
			hqlCount.append(" and bb.bakPurId = :bakPurId ");
		}
		if(StringUtils.isNotEmpty(compId)){
			hql.append(" and bb.compId like :compId ");
			hqlCount.append(" and bb.compId like :compId ");
		}
		if(StringUtils.isNotEmpty(hospName)){
			hql.append(" and bb.hospName like :hospName ");
			hqlCount.append(" and bb.hospName like :hospName ");
		}
		
		if(StringUtils.isNotEmpty(bakHospId)){
			hql.append(" and bb.bakHospId = :bakHospId ");
			hqlCount.append(" and bb.bakHospId = :bakHospId ");
		}else{
			hql.append(" and bb.auditStatus != 0 ");
			hqlCount.append(" and bb.auditStatus != 0 ");
		}
		if(null !=  auditStatus){
			hql.append(" and bb.auditStatus = :auditStatus ");
			hqlCount.append(" and bb.auditStatus = :auditStatus ");
		}
		if(null != isLongtimePlan){
			hql.append(" and bb.isLongtimePlan = :isLongtimePlan ");
			hqlCount.append(" and bb.isLongtimePlan = :isLongtimePlan ");
		}
		if(StringUtils.isNotEmpty(drugName)){
			hql.append(" and bb.drugName like :drugName ");
			hqlCount.append(" and bb.drugName like :drugName ");
		}
		if(StringUtils.isNotEmpty(formId)){
			hql.append(" and bb.formId like :formId ");
			hqlCount.append(" and bb.formId like :formId ");
		}
		if(StringUtils.isNotEmpty(specId)){
			hql.append(" and bb.specId like :specId ");
			hqlCount.append(" and bb.specId like :specId ");
		}
		
		if(StringUtils.isNotEmpty(bakSubmitUser)){
			hql.append(" and bb.bakSubmitUser like :bakSubmitUser ");
			hqlCount.append(" and bb.bakSubmitUser like :bakSubmitUser ");
		}
		if(null != date1){
			hql.append(" and :date1 <= bb.auditDatetime ");
			hqlCount.append(" and :date1 <= bb.auditDatetime ");
		}
		if(null != date2){
			hql.append(" and bb.auditDatetime <= :date2 ");
			hqlCount.append(" and bb.auditDatetime <= :date2 ");
		}
		Query query = this.getSession().createQuery(hql.toString());
		Query queryCount = this.getSession().createQuery(hqlCount.toString());
		if(StringUtils.isNotEmpty(bakPurId)){
			query.setParameter("bakPurId", bakPurId);
			queryCount.setParameter("bakPurId", bakPurId);
		}
		if(StringUtils.isNotEmpty(compId)){
			query.setParameter("compId", "%"+compId+"%");
			queryCount.setParameter("compId", "%"+compId+"%");
		}
		if(StringUtils.isNotEmpty(hospName)){
			query.setParameter("hospName", "%"+hospName+"%");
			queryCount.setParameter("hospName", "%"+hospName+"%");
		}
		if(StringUtils.isNotEmpty(bakHospId)){
			query.setParameter("bakHospId", bakHospId);
			queryCount.setParameter("bakHospId", bakHospId);
		}
		if(null !=  auditStatus){
			query.setShort("auditStatus",auditStatus);
			queryCount.setShort("auditStatus",auditStatus);
		}
		if(null != isLongtimePlan){
			query.setShort("isLongtimePlan",isLongtimePlan);
			queryCount.setShort("isLongtimePlan",isLongtimePlan);
		}
		if(StringUtils.isNotEmpty(drugName)){
			query.setParameter("drugName","%"+drugName+"%");
			queryCount.setParameter("drugName","%"+drugName+"%");
		}
		if(StringUtils.isNotEmpty(formId)){
			query.setParameter("formId","%"+formId+"%");
			queryCount.setParameter("formId","%"+formId+"%");
		}
		if(StringUtils.isNotEmpty(specId)){
			query.setParameter("specId","%"+specId+"%");
			queryCount.setParameter("specId","%"+specId+"%");
		}
		if(StringUtils.isNotEmpty(bakSubmitUser)){
			query.setParameter("bakSubmitUser", "%"+bakSubmitUser+"%");
			queryCount.setParameter("bakSubmitUser", "%"+bakSubmitUser+"%");
		}
		if(null != date1){
			query.setDate("date1", date1);
			queryCount.setDate("date1", date1);
		}
		if(null != date2){
			query.setDate("date2", date2);
			queryCount.setDate("date2", date2);
		}
		viewBakpurchase.setCount(Long.valueOf(queryCount.list().get(0)+""));
		query.setFirstResult((page.getPage()-1)*page.getMaxResults());
		query.setMaxResults(page.getMaxResults());
		return query.list();
		}catch(Exception e){
			log.error("查询备案审批信息错误-->", e);
			return new ArrayList<ViewBakpurchase>();
		}
	}
	
	/**
	 * @category 备案审核结果保存
	 * @author 韩守松
	 * @date   2015年6月23日
	 * @param  @param baseBakpurchase
	 * @param  @return
	 */
	public boolean dataAddAndEdit(BaseBakpurchase baseBakpurchase) {
		try{
			String bakPurId = baseBakpurchase.getBakPurId();
			Short auditStatus = baseBakpurchase.getAuditStatus();
			String auditRemaks = baseBakpurchase.getAuditRemaks();
			StringBuffer hql = new StringBuffer();
			hql.append("update BaseBakpurchase bb set bb.auditStatus = :auditStatus ,bb.auditRemaks = :auditRemaks where bb.bakPurId = :bakPurId ");
			Query query = this.getSession().createQuery(hql.toString());
			query.setParameter("bakPurId", bakPurId);
			query.setShort("auditStatus", auditStatus);
			query.setParameter("auditRemaks", auditRemaks);
			query.executeUpdate();
		this.getSession().flush();
		return true;
		}catch(Exception e){
			log.error("备案结果保存出错-->", e);
			return false;
		}
	}
	
	@SuppressWarnings({ "unchecked", "unused" })
	public List<ViewBakpurchase> getBaseBakpurchaseList(Pagination page, ViewBakpurchase viewBakpurchase){

		String drugCode = page.getConditions().get("drugCode");
		String drugName = page.getConditions().get("drugName");
		String compName = page.getConditions().get("compName");
		String hospName = page.getConditions().get("hospName");
		String auditStatus = page.getConditions().get("auditStatus");
		String appStartDate = page.getConditions().get("appStartDate");
		String appEndDate = page.getConditions().get("appEndDate");
		String areaId = page.getConditions().get("areaId");
		String auditStartDate = page.getConditions().get("auditStartDate");
		String auditEndDate = page.getConditions().get("auditEndDate");
		
		StringBuffer hql = new StringBuffer();
		StringBuffer hqlCount = new StringBuffer();

		hql.append("from ViewBakpurchase bb where 1=1 ");
		hqlCount.append("select count(*) from  ViewBakpurchase bb where 1=1 ");

		if (StringUtils.isNotBlank(drugCode)) {
			hql.append(" and bb.drugCode = :drugCode ");
			hqlCount.append(" and bb.drugCode = :drugCode ");
		}
		
		if (StringUtils.isNotBlank(drugName)) {
			hql.append(" and bb.drugName like :drugName ");
			hqlCount.append(" and bb.drugName like :drugName ");
		}
		
		if (StringUtils.isNotBlank(compName)) {
			hql.append(" and bb.compName like :compName ");
			hqlCount.append(" and bb.compName like :compName ");
		}
		
		if (StringUtils.isNotBlank(hospName)) {
			hql.append(" and bb.hospName like :hospName ");
			hqlCount.append(" and bb.hospName like :hospName ");
		}
		
		if (StringUtils.isNotBlank(auditStatus)) {
			hql.append(" and bb.auditStatus = :auditStatus ");
			hqlCount.append(" and bb.auditStatus = :auditStatus ");
		} else {
			hql.append(" and bb.auditStatus != 0 ");
			hqlCount.append(" and bb.auditStatus != 0 ");
		}
		
		if (StringUtils.isNotBlank(areaId)) {
			hql.append(" and bb.areaId like :areaId ");
			hqlCount.append(" and bb.areaId like :areaId ");
		}
		
//		if (StringUtils.isNotBlank(appStartDate)) {
//			hql.append(" and bb.bakSubmitDatetime >= :appStartDate ");
//			hqlCount.append(" and bb.bakSubmitDatetime >= :appStartDate ");
//		}
//		
//		if (StringUtils.isNotBlank(appEndDate)) {
//			hql.append(" and bb.bakSubmitDatetime <= :appEndDate ");
//			hqlCount.append(" and bb.bakSubmitDatetime <= :appEndDate ");
//		}
		
		if (StringUtils.isNotBlank(auditStartDate)) {
			hql.append(" and bb.auditDatetime >= :auditStartDate ");
			hqlCount.append(" and bb.auditDatetime >= :auditStartDate ");
		}
		
		if (StringUtils.isNotBlank(auditEndDate)) {
			hql.append(" and bb.auditDatetime <= :auditEndDate ");
			hqlCount.append(" and bb.auditDatetime <= :auditEndDate ");
		}
		
		Query query = this.getSession().createQuery(hql.toString());
		Query queryCount = this.getSession().createQuery(hqlCount.toString());
		if(StringUtils.isNotEmpty(drugCode)){
			query.setParameter("drugCode", drugCode);
			queryCount.setParameter("drugCode", drugCode);
		}
		
		if(StringUtils.isNotEmpty(drugName)){
			query.setParameter("drugName", "%" + drugName + "%");
			queryCount.setParameter("drugName", "%" + drugName + "%");
		}
		
		if(StringUtils.isNotEmpty(compName)){
			query.setParameter("compName", "%" + compName + "%");
			queryCount.setParameter("compName", "%" + compName + "%");
		}
		
		if(StringUtils.isNotEmpty(hospName)){
			query.setParameter("hospName", "%" + hospName + "%");
			queryCount.setParameter("hospName", "%" + hospName + "%");
		}
		
		if (StringUtils.isNotEmpty(auditStatus)) {
			query.setShort("auditStatus", Short.parseShort(auditStatus));
			queryCount.setShort("auditStatus", Short.parseShort(auditStatus));
		}
		
		if(StringUtils.isNotEmpty(areaId)){
			query.setParameter("areaId", "%" + areaId + "%");
			queryCount.setParameter("areaId", "%" + areaId + "%");
		}
		/*
		if(StringUtils.isNotBlank(appStartDate)){
			query.setDate("appStartDate",  ParseDate.fromFullFormat(appStartDate + " 00:00:00"));
			queryCount.setDate("appStartDate",  ParseDate.fromFullFormat(appStartDate + " 00:00:00"));
		}
		
		if(StringUtils.isNotBlank(appEndDate)){
			query.setDate("appEndDate", ParseDate.fromFullFormat(appEndDate + " 23:59:59"));
			queryCount.setDate("appEndDate",  ParseDate.fromFullFormat(appEndDate + " 23:59:59"));
		}
		*/
		if(StringUtils.isNotBlank(auditStartDate)){
			query.setDate("auditStartDate", ParseDate.fromFullFormat(auditStartDate + " 00:00:00"));
			queryCount.setDate("auditStartDate",  ParseDate.fromFullFormat(auditStartDate + " 00:00:00"));
		}
		
		if(StringUtils.isNotBlank(auditEndDate)){
			query.setDate("auditEndDate", ParseDate.fromFullFormat(auditEndDate + " 23:59:59"));
			queryCount.setDate("auditEndDate",  ParseDate.fromFullFormat(auditEndDate + " 23:59:59"));
		}
		
		viewBakpurchase.setCount(Long.valueOf(queryCount.list().get(0)+""));
		query.setFirstResult((page.getPage()-1)*page.getMaxResults());
		query.setMaxResults(page.getMaxResults());
		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	public List<ViewBakpurchase> getHospBaseBakpurchaseList(Pagination page, ViewBakpurchase viewBakpurchase,SysUser user){
		String id = user.getId();
		String drugCode = viewBakpurchase.getDrugCode();
		String drugName = viewBakpurchase.getDrugName();
		String compName = viewBakpurchase.getCompName();
		String hospName = viewBakpurchase.getHospName();
		String auditStatus = page.getConditions().get("auditStatus");
		String appStartDate = page.getConditions().get("appStartDate");
		String appEndDate = page.getConditions().get("appEndDate");
		String auditStartDate = page.getConditions().get("startTime");
		String auditEndDate = page.getConditions().get("endTime");
		
		StringBuffer hql = new StringBuffer();
		StringBuffer hqlCount = new StringBuffer();

		hql.append("from ViewBakpurchase bb where 1=1 and bb.bakHospId = :id ");
		hqlCount.append("select count(*) from  ViewBakpurchase bb where 1=1 and bb.bakHospId = :id ");

		if (StringUtils.isNotBlank(drugCode)) {
			hql.append(" and bb.drugCode = :drugCode ");
			hqlCount.append(" and bb.drugCode = :drugCode ");
		}
		
		if (StringUtils.isNotBlank(drugName)) {
			hql.append(" and bb.drugName like :drugName ");
			hqlCount.append(" and bb.drugName like :drugName ");
		}
		
		if (StringUtils.isNotBlank(compName)) {
			hql.append(" and bb.compName like :compName ");
			hqlCount.append(" and bb.compName like :compName ");
		}
		
		if (StringUtils.isNotBlank(hospName)) {
			hql.append(" and bb.hospName like :hospName ");
			hqlCount.append(" and bb.hospName like :hospName ");
		}
		
		if (StringUtils.isNotBlank(auditStatus)) {
			hql.append(" and bb.auditStatus = :auditStatus ");
			hqlCount.append(" and bb.auditStatus = :auditStatus ");
		} 
		
		
		if (StringUtils.isNotBlank(appStartDate)) {
			hql.append(" and bb.bakSubmitDatetime >= :appStartDate ");
			hqlCount.append(" and bb.bakSubmitDatetime >= :appStartDate ");
		}
		
		if (StringUtils.isNotBlank(appEndDate)) {
			hql.append(" and bb.bakSubmitDatetime <= :appEndDate ");
			hqlCount.append(" and bb.bakSubmitDatetime <= :appEndDate ");
		}
		
		if (StringUtils.isNotBlank(auditStartDate)) {
			hql.append(" and bb.auditDatetime >= :auditStartDate ");
			hqlCount.append(" and bb.auditDatetime >= :auditStartDate ");
		}
		
		if (StringUtils.isNotBlank(auditEndDate)) {
			hql.append(" and bb.auditDatetime <= :auditEndDate ");
			hqlCount.append(" and bb.auditDatetime <= :auditEndDate ");
		}
		
		hql.append(" order by bb.bakSubmitDatetime desc ");
		
		
		Query query = this.getSession().createQuery(hql.toString());
		Query queryCount = this.getSession().createQuery(hqlCount.toString());
		query.setString("id", id);
		queryCount.setString("id", id);
		if(StringUtils.isNotEmpty(drugCode)){
			query.setParameter("drugCode", drugCode);
			queryCount.setParameter("drugCode", drugCode);
		}
		
		if(StringUtils.isNotEmpty(drugName)){
			query.setParameter("drugName", "%" + drugName + "%");
			queryCount.setParameter("drugName", "%" + drugName + "%");
		}
		
		if(StringUtils.isNotEmpty(compName)){
			query.setParameter("compName", "%" + compName + "%");
			queryCount.setParameter("compName", "%" + compName + "%");
		}
		
		if(StringUtils.isNotEmpty(hospName)){
			query.setParameter("hospName", "%" + hospName + "%");
			queryCount.setParameter("hospName", "%" + hospName + "%");
		}
		
		if (StringUtils.isNotEmpty(auditStatus)) {
			query.setShort("auditStatus", Short.parseShort(auditStatus));
			queryCount.setShort("auditStatus", Short.parseShort(auditStatus));
		}
		
		if(StringUtils.isNotBlank(appStartDate)){
			query.setTimestamp("appStartDate",  ParseDate.fromFullFormat(appStartDate + " 00:00:00"));
			queryCount.setTimestamp("appStartDate",  ParseDate.fromFullFormat(appStartDate + " 00:00:00"));
		}
		
		if(StringUtils.isNotBlank(appEndDate)){
			query.setTimestamp("appEndDate", ParseDate.fromFullFormat(appEndDate + " 23:59:59"));
			queryCount.setTimestamp("appEndDate",  ParseDate.fromFullFormat(appEndDate + " 23:59:59"));
		}

		if(StringUtils.isNotBlank(auditStartDate)){
			query.setTimestamp("auditStartDate", ParseDate.fromFullFormat(auditStartDate + " 00:00:00"));
			queryCount.setTimestamp("auditStartDate",  ParseDate.fromFullFormat(auditStartDate + " 00:00:00"));
		}
		
		if(StringUtils.isNotBlank(auditEndDate)){
			query.setTimestamp("auditEndDate", ParseDate.fromFullFormat(auditEndDate + " 23:59:59"));
			queryCount.setTimestamp("auditEndDate",  ParseDate.fromFullFormat(auditEndDate + " 23:59:59"));
		}

		viewBakpurchase.setCount(Long.valueOf(queryCount.list().get(0)+""));
		query.setFirstResult((page.getPage()-1)*page.getMaxResults());
		query.setMaxResults(page.getMaxResults());
		return query.list();
	}
	
	/**
	 * @category 中心卫生局医院对备案申请结果查询
	 * @date 2015年11月20日
	 * @param page
	 * @return
	 */
	public Pagination getBakPurchaseList(Pagination page, SysUser user) {
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT a.bak_pur_id bakPurId, a.drug_code drugCode, c.qualityspec_name drugName, ");
		sb.append(" 	c.form_name formName, c.spec_name specName, b.unit_name unitName,  ");
		sb.append(" 	b.material_name materialName, b.factor, b.comp_code compCode,  ");
		sb.append(" 	b.comp_name compName, a.del_code delCode, d.comp_name delName, ");
		sb.append(" 	a.pur_price purPrice, a.pur_amount purAmount, a.is_longtime_plan isLongtimePlan, ");
		sb.append(" 	DATE_FORMAT(a.bak_submit_datetime, '%Y-%m-%d') bakSubmitDatetime,  ");
		sb.append(" 	DATE_FORMAT(a.audit_datetime, '%Y-%m-%d') auditDatetime,  ");
		sb.append(" 	DATE_FORMAT(a.reaudit_datetime, '%Y-%m-%d') reauditDatetime,  ");
		sb.append(" 	a.audit_status auditStatus, e.hosp_code hospCode, e.hosp_name hospName, ");
		sb.append(" 	a.remark, a.bak_reason bakReason, a.audit_remaks auditRemaks, a.reaudit_remaks reauditRemaks ");
		sb.append(" FROM base_bakpurchase a ");
		sb.append(" JOIN std_proddruginfo b ON a.drug_code = b.drug_code ");
		sb.append(" JOIN std_drugqualityspec c ON b.qualityspec_id = c.qualityspec_id ");
		sb.append(" LEFT JOIN std_compinfo d ON a.del_code = d.comp_code ");
		sb.append(" LEFT JOIN std_hospinfo e ON a.bak_hosp_code = e.hosp_code ");
		
		if (user.getType() == UserType.hszx.getKey()) {	//	卫生局
			sb.append(" JOIN ( ");
			sb.append("   SELECT DISTINCT org_code FROM sys_mgr_relation WHERE mgr_org_code = :orgCode ");
			sb.append("   AND mgr_type LIKE '%A1%' ");
			sb.append(" ) z ON a.bak_hosp_code = z.org_code ");
		}
		
		sb.append(" WHERE b.`status` = 1 AND c.`status` = 1 ");
		
		ViewBakpurchase bak = (ViewBakpurchase) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		
		if (user.getType() == UserType.hszx.getKey()) {	//	卫生局
			StdHealthbureau hb = user.getInfo();
			if (hb == null) {
				return page;
			}
			params.put("orgCode", hb.getHeaBurCode());
		}
		
		if (bak != null) {
			if (StringUtils.isNotBlank(bak.getHospCode())) {
				sb.append(" AND a.bak_hosp_code = :hospCode ");
				params.put("hospCode", bak.getHospCode());
			}
			if (StringUtils.isNotBlank(bak.getDrugCode())) {
				sb.append(" AND a.drug_code = :drugCode ");
				params.put("drugCode", bak.getDrugCode());
			}
			if (StringUtils.isNotBlank(bak.getDrugName())) {
				sb.append(" AND (c.qualityspec_name LIKE :drugName OR c.qualityspec_name_spel LIKE :drugName) ");
				params.put("drugName", "%" + bak.getDrugName() + "%");
			}
			if (StringUtils.isNotBlank(bak.getCompName())) {
				sb.append(" AND (b.comp_name LIKE :compName OR b.comp_name_spel LIKE :compName) ");
				params.put("compName", "%" + bak.getCompName() + "%");
			}
			if (StringUtils.isNotBlank(bak.getHospName())) {
				sb.append(" AND (e.hosp_name LIKE :hospName OR e.hosp_spel_code LIKE :hospName) ");
				params.put("hospName", "%" + bak.getHospName() + "%");
			}
			if (StringUtils.isNotBlank(bak.getSubmitStartDate())) {
				sb.append(" AND DATE_FORMAT(a.bak_submit_datetime, '%Y-%m-%d') >= :submitStartDate ");
				params.put("submitStartDate", bak.getSubmitStartDate());
			}
			if (StringUtils.isNotBlank(bak.getSubmitEndDate())) {
				sb.append(" AND DATE_FORMAT(a.bak_submit_datetime, '%Y-%m-%d') <= :submitEndDate ");
				params.put("submitEndDate", bak.getSubmitEndDate());
			}
			if (bak.getAuditStatus() != null) {
				sb.append(" AND a.audit_status = :auditStatus ");
				params.put("auditStatus", bak.getAuditStatus());
			}
		}
		
		dao.getPageMapList(sb.toString(), params, page);
		
		return page;
	}
	
	/**
	 * @category 根据id查询备案详情
	 * @date 2015年11月20日
	 * @param id
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Map<String, Object> getBakDetailById(String id) {
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT a.bak_pur_id bakPurId, a.drug_code drugCode, c.qualityspec_name drugName, ");
		sb.append(" 	c.form_name formName, c.spec_name specName, b.unit_name unitName,  ");
		sb.append(" 	b.material_name materialName, b.factor, b.comp_code compCode, b.bus_name busName,  ");
		sb.append(" 	b.comp_name compName, a.del_code delCode, d.comp_name delName, b.approval_code approvalCode, ");
		sb.append(" 	a.pur_price purPrice, a.pur_amount purAmount, a.is_longtime_plan isLongtimePlan, ");
		sb.append(" 	DATE_FORMAT(a.bak_submit_datetime, '%Y-%m-%d') bakSubmitDatetime,  ");
		sb.append(" 	DATE_FORMAT(a.audit_datetime, '%Y-%m-%d') auditDatetime,  ");
		sb.append(" 	DATE_FORMAT(a.reaudit_datetime, '%Y-%m-%d') reauditDatetime,  ");
		sb.append(" 	a.audit_status auditStatus, e.hosp_code hospCode, e.hosp_name hospName, ");
		sb.append(" 	a.remark, a.bak_reason bakReason, a.audit_remaks auditRemaks, a.reaudit_remaks reauditRemaks ");
		sb.append(" FROM base_bakpurchase a ");
		sb.append(" JOIN std_proddruginfo b ON a.drug_code = b.drug_code ");
		sb.append(" JOIN std_drugqualityspec c ON b.qualityspec_id = c.qualityspec_id ");
		sb.append(" LEFT JOIN std_compinfo d ON a.del_code = d.comp_code ");
		sb.append(" LEFT JOIN std_hospinfo e ON a.bak_hosp_code = e.hosp_code ");
		sb.append(" WHERE b.`status` = 1 AND c.`status` = 1 ");
		sb.append(" AND a.bak_pur_id = :bakPurId ");
		
		Session session = getSession();
		SQLQuery query = session.createSQLQuery(sb.toString());
		
		query.setString("bakPurId", id);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		
		return (Map<String, Object>) query.uniqueResult();
	}
	
	/**
	 * @category 查询待复审的备案申请列表
	 * @date 2015年11月20日
	 * @param page
	 * @return
	 */
	public Pagination getReauditBakList(Pagination page, SysUser user) {
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT a.bak_pur_id bakPurId, a.drug_code drugCode, c.qualityspec_name drugName, ");
		sb.append(" 	c.form_name formName, c.spec_name specName, b.unit_name unitName,  ");
		sb.append(" 	b.material_name materialName, b.factor, b.comp_code compCode,  ");
		sb.append(" 	b.comp_name compName, a.del_code delCode, d.comp_name delName, ");
		sb.append(" 	a.pur_price purPrice, a.pur_amount purAmount, a.is_longtime_plan isLongtimePlan, ");
		sb.append(" 	DATE_FORMAT(a.bak_submit_datetime, '%Y-%m-%d') bakSubmitDatetime,  ");
		sb.append(" 	DATE_FORMAT(a.audit_datetime, '%Y-%m-%d') auditDatetime,  ");
		sb.append(" 	DATE_FORMAT(a.reaudit_datetime, '%Y-%m-%d') reauditDatetime,  ");
		sb.append(" 	a.audit_status auditStatus, e.hosp_code hospCode, e.hosp_name hospName, ");
		sb.append(" 	a.remark, a.bak_reason bakReason, a.audit_remaks auditRemaks, a.reaudit_remaks reauditRemaks ");
		sb.append(" FROM base_bakpurchase a ");
		sb.append(" JOIN std_proddruginfo b ON a.drug_code = b.drug_code ");
		sb.append(" JOIN std_drugqualityspec c ON b.qualityspec_id = c.qualityspec_id ");
		sb.append(" LEFT JOIN std_compinfo d ON a.del_code = d.comp_code ");
		sb.append(" LEFT JOIN std_hospinfo e ON a.bak_hosp_code = e.hosp_code ");
		sb.append(" JOIN ( ");
		sb.append("   SELECT DISTINCT org_code, mgr_type FROM sys_mgr_relation WHERE mgr_org_code = :orgCode ");
		sb.append("   AND (mgr_type LIKE '%A1%' OR mgr_type LIKE '%A2%') ");
		sb.append(" ) z ON a.bak_hosp_code = z.org_code ");
		sb.append(" WHERE b.`status` = 1 AND c.`status` = 1 ");
		sb.append(" AND ( ");
		sb.append("   (a.audit_status = 1 AND z.mgr_type LIKE '%A1%') OR ");	//	中心审核的医疗机构
		sb.append("   (a.audit_status = 2 AND z.mgr_type LIKE '%A2%' ");	//	中心复审的医疗机构
		
		if ("1".equals(bakTamperary) && "0".equals(bakLongterm)) {	//	仅复审临时备案
			sb.append(" AND a.is_longtime_plan = 0 ");
		} else if ("1".equals(bakLongterm) && "0".equals(bakTamperary)) {	//	仅复审长期备案
			sb.append(" AND a.is_longtime_plan = 1 ");
		} else if ("0".equals(bakLongterm) && "0".equals(bakTamperary)) {	//	不复审
			sb.append(" AND a.is_longtime_plan = 2 ");
		}
		
		sb.append(" )) ");
		
		ViewBakpurchase bak = (ViewBakpurchase) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		
		if (user.getType() == UserType.zx.getKey()) {	//	中心
			StdHealthbureau hb = user.getInfo();
			if (hb == null) {
				return page;
			}
			params.put("orgCode", hb.getHeaBurCode());
		}
		
		if (bak != null) {
			if (StringUtils.isNotBlank(bak.getHospCode())) {
				sb.append(" AND a.bak_hosp_code = :hospCode ");
				params.put("hospCode", bak.getHospCode());
			}
			if (StringUtils.isNotBlank(bak.getDrugCode())) {
				sb.append(" AND a.drug_code = :drugCode ");
				params.put("drugCode", bak.getDrugCode());
			}
			if (StringUtils.isNotBlank(bak.getDrugName())) {
				sb.append(" AND (c.qualityspec_name LIKE :drugName OR c.qualityspec_name_spel LIKE :drugName) ");
				params.put("drugName", "%" + bak.getDrugName() + "%");
			}
			if (StringUtils.isNotBlank(bak.getCompName())) {
				sb.append(" AND (b.comp_name LIKE :compName OR b.comp_name_spel LIKE :compName) ");
				params.put("compName", "%" + bak.getCompName() + "%");
			}
			if (StringUtils.isNotBlank(bak.getHospName())) {
				sb.append(" AND (e.hosp_name LIKE :hospName OR e.hosp_spel_code LIKE :hospName) ");
				params.put("hospName", "%" + bak.getHospName() + "%");
			}
			if (StringUtils.isNotBlank(bak.getSubmitStartDate())) {
				sb.append(" AND DATE_FORMAT(a.bak_submit_datetime, '%Y-%m-%d') >= :submitStartDate ");
				params.put("submitStartDate", bak.getSubmitStartDate());
			}
			if (StringUtils.isNotBlank(bak.getSubmitEndDate())) {
				sb.append(" AND DATE_FORMAT(a.bak_submit_datetime, '%Y-%m-%d') <= :submitEndDate ");
				params.put("submitEndDate", bak.getSubmitEndDate());
			}
			if (bak.getAuditStatus() != null) {
				sb.append(" AND a.audit_status = :auditStatus ");
				params.put("auditStatus", bak.getAuditStatus());
			}
		}
		
		dao.getPageMapList(sb.toString(), params, page);
		
		return page;
	}
	
	/**
	 * @category 卫生局审批备案列表
	 * @date 2015年11月20日
	 * @param page
	 * @return
	 */
	public Pagination getAuditBakList(Pagination page, SysUser user) {
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT a.bak_pur_id bakPurId, a.drug_code drugCode, c.qualityspec_name drugName, ");
		sb.append(" 	c.form_name formName, c.spec_name specName, b.unit_name unitName,  ");
		sb.append(" 	b.material_name materialName, b.factor, b.comp_code compCode,  ");
		sb.append(" 	b.comp_name compName, a.del_code delCode, d.comp_name delName, ");
		sb.append(" 	a.pur_price purPrice, a.pur_amount purAmount, a.is_longtime_plan isLongtimePlan, ");
		sb.append(" 	DATE_FORMAT(a.bak_submit_datetime, '%Y-%m-%d') bakSubmitDatetime,  ");
		sb.append(" 	DATE_FORMAT(a.audit_datetime, '%Y-%m-%d') auditDatetime,  ");
		sb.append(" 	DATE_FORMAT(a.reaudit_datetime, '%Y-%m-%d') reauditDatetime,  ");
		sb.append(" 	a.audit_status auditStatus, e.hosp_code hospCode, e.hosp_name hospName, ");
		sb.append(" 	a.remark, a.bak_reason bakReason, a.audit_remaks auditRemaks, a.reaudit_remaks reauditRemaks ");
		sb.append(" FROM base_bakpurchase a ");
		sb.append(" JOIN std_proddruginfo b ON a.drug_code = b.drug_code ");
		sb.append(" JOIN std_drugqualityspec c ON b.qualityspec_id = c.qualityspec_id ");
		sb.append(" LEFT JOIN std_compinfo d ON a.del_code = d.comp_code ");
		sb.append(" LEFT JOIN std_hospinfo e ON a.bak_hosp_code = e.hosp_code ");
		sb.append(" JOIN ( ");
		sb.append("   SELECT DISTINCT org_code, mgr_type FROM sys_mgr_relation WHERE mgr_org_code = :orgCode ");
		sb.append("   AND mgr_type LIKE '%A1%' ");
		sb.append(" ) z ON a.bak_hosp_code = z.org_code ");
		sb.append(" WHERE b.`status` = 1 AND c.`status` = 1 ");
		sb.append(" AND a.audit_status = 1 ");
		
		ViewBakpurchase bak = (ViewBakpurchase) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		
		if (user.getType() == UserType.hszx.getKey()) {	//	卫生局
			StdHealthbureau hb = user.getInfo();
			if (hb == null) {
				return page;
			}
			params.put("orgCode", hb.getHeaBurCode());
		}
		
		if (bak != null) {
			if (StringUtils.isNotBlank(bak.getHospCode())) {
				sb.append(" AND a.bak_hosp_code = :hospCode ");
				params.put("hospCode", bak.getHospCode());
			}
			if (StringUtils.isNotBlank(bak.getDrugCode())) {
				sb.append(" AND a.drug_code = :drugCode ");
				params.put("drugCode", bak.getDrugCode());
			}
			if (StringUtils.isNotBlank(bak.getDrugName())) {
				sb.append(" AND (c.qualityspec_name LIKE :drugName OR c.qualityspec_name_spel LIKE :drugName) ");
				params.put("drugName", "%" + bak.getDrugName() + "%");
			}
			if (StringUtils.isNotBlank(bak.getCompName())) {
				sb.append(" AND (b.comp_name LIKE :compName OR b.comp_name_spel LIKE :compName) ");
				params.put("compName", "%" + bak.getCompName() + "%");
			}
			if (StringUtils.isNotBlank(bak.getHospName())) {
				sb.append(" AND (e.hosp_name LIKE :hospName OR e.hosp_spel_code LIKE :hospName) ");
				params.put("hospName", "%" + bak.getHospName() + "%");
			}
			if (StringUtils.isNotBlank(bak.getSubmitStartDate())) {
				sb.append(" AND DATE_FORMAT(a.bak_submit_datetime, '%Y-%m-%d') >= :submitStartDate ");
				params.put("submitStartDate", bak.getSubmitStartDate());
			}
			if (StringUtils.isNotBlank(bak.getSubmitEndDate())) {
				sb.append(" AND DATE_FORMAT(a.bak_submit_datetime, '%Y-%m-%d') <= :submitEndDate ");
				params.put("submitEndDate", bak.getSubmitEndDate());
			}
			if (bak.getAuditStatus() != null) {
				sb.append(" AND a.audit_status = :auditStatus ");
				params.put("auditStatus", bak.getAuditStatus());
			}
		}
		
		dao.getPageMapList(sb.toString(), params, page);
		
		return page;
	}
	
}
